#!/usr/bin/python
# -*- coding: UTF-8 -*-
# author:heqiang

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean, TIMESTAMP, and_, desc
from sqlalchemy.orm import sessionmaker
import datetime

engine = sqlalchemy.create_engine('sqlite:///sqlite.db')

base = declarative_base()

Session = sessionmaker(bind=engine)
session = Session()


class TklRecord(base):
    # 指定本类映射到TklRecord表
    __tablename__ = 'TklRecord'

    id = Column(Integer, primary_key=True, autoincrement=True)
    # 指定name映射到name字段; name字段为字符串类形，
    username = Column(String(32))
    user_id = Column(String(32))
    createtime = Column(TIMESTAMP)
    tkl = Column(String(128))
    tao_id = Column(String(32))
    fanyong = Column(Integer)
    count = Column(Integer, default=0)  # 关联一个订单+1


class UserRecord(base):
    # 指定本类映射到TklRecord表
    __tablename__ = 'UserRecord'

    # id = Column(Integer, primary_key=True, autoincrement=True)
    # 指定name映射到name字段; name字段为字符串类形，
    username = Column(String(32), )
    user_id = Column(String(32), primary_key=True)
    fanli = Column(Integer, default=0)
    createtime = Column(TIMESTAMP)


class OrderRecord(base):
    # 指定本类映射到OrderRecord表
    __tablename__ = 'OrderRecord'

    tao_id = Column(String(32), )
    trade_id = Column(String(32), primary_key=True)
    fanli = Column(Integer)
    createtime = Column(TIMESTAMP)
    flag = Column(Boolean)  # 是否关联


def create_table():
    base.metadata.create_all(engine, checkfirst=True)


def add_user(username, user_id, fanli=0):
    now_time = datetime.datetime.now()
    new_user = UserRecord(username=username, user_id=user_id, fanli=fanli, createtime=now_time)
    session.add(new_user)
    session.commit()


def add_tkl_record(username, user_id, re_time, tkl, tao_id, fanyong):
    if isinstance(re_time, str):
        re_time = datetime.datetime.strptime(re_time, '%Y-%m-%d %H:%M:%S')
    add_tkl = TklRecord(username=username, user_id=user_id, createtime=re_time, tkl=tkl, tao_id=tao_id, fanyong=fanyong)
    session.add(add_tkl)
    session.commit()


def get_tkl_record(tao_id, createtime,distance=15):
    """
    对应时间段和tao_id的tkl
    """
    if isinstance(createtime, str):
        createtime = datetime.datetime.strptime(createtime, '%Y-%m-%d %H:%M:%S')

    createtime2 = createtime - datetime.timedelta(minutes=distance)

    tkls = session.query(TklRecord).filter(
        and_(TklRecord.tao_id == tao_id, TklRecord.createtime <= createtime,
             TklRecord.createtime >= createtime2)).order_by(desc(TklRecord.createtime)).all()
    if len(tkls) == 0:
        plus_distance = distance*2
        print(plus_distance)
        if plus_distance > 60:
            return False
        tkl = get_tkl_record(tao_id, createtime,plus_distance)
        return tkl
    else:
        tkl = tkls[0]
        return [tkl.id,tkl.username, tkl.user_id, tkl.fanyong, tkl.tao_id,tkl.count]

def update_tkl_count(id,count):
    session.query(TklRecord).filter_by(id=id).update({TklRecord.count: count})
    session.commit()

def get_user_fanli(user_id):
    fanli = session.query(UserRecord).filter_by(user_id=user_id).first()
    return fanli


def check_user(user_id):
    user = session.query(UserRecord).filter_by(user_id=user_id).first()
    return [user.username, user.user_id, user.fanli, user.createtime]


def add_user_fanli(user_id, fanyong):
    mod_user = session.query(UserRecord).filter_by(user_id=user_id).first()
    total = mod_user.fanli
    total += fanyong
    mod_user.fanli = total
    session.commit()


def reset_user_fanli(user_id):
    session.query(UserRecord).filter_by(user_id=user_id).update({UserRecord.fanli: 0})
    session.commit()


def add_order(tao_id, trade_id, fanli, createtime, flag=False):
    if isinstance(createtime, str):
        createtime = datetime.datetime.strptime(createtime, '%Y-%m-%d %H:%M:%S')
    addorder = OrderRecord(tao_id=tao_id, trade_id=trade_id, fanli=fanli, createtime=createtime, flag=flag)
    session.add(addorder)
    session.commit()

def update_oder_flag(trade_id):
    session.query(OrderRecord).filter_by(trade_id=trade_id).update({OrderRecord.flag: True})
    session.commit()

if __name__ == "__main__":
    pass
    # create_table()
    # now_time = datetime.datetime.now()
    # now_time = now_time.strftime('%Y-%m-%d %H:%M:%S')
    # add_tkl_record("username", "user_id", now_time, "tkl-edwd2f232323f2445@#$^&*(", "tao_id", 1.2)
    # add_user(username="test1",user_id="2")
    # add_user(username="user234",user_id="32")
    # add_user(username="test3",user_id="5")
    # add_user_fanli ("2", 2)
    # reset_user_fanli("2")
    # a = check_user(user_id="2")
    # add_order(tao_id="608054988202",trade_id="341324312",fanli="0.5",createtime="2022-02-22 16:44:46")
    # print(a)
    fanyong = get_tkl_record(tao_id="608054988202", createtime="2022-02-22 16:14:46")
    print(fanyong)
